package org.jeecg.modules.online.cgreport.util;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.pagination.DialectFactory;
import com.baomidou.mybatisplus.extension.plugins.pagination.DialectModel;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.plugins.pagination.dialects.IDialect;
import com.baomidou.mybatisplus.extension.toolkit.JdbcUtils;
import lombok.extern.slf4j.Slf4j;
import org.jeecg.common.system.query.QueryGenerator;
import org.jeecg.common.system.vo.DynamicDataSourceModel;
import org.jeecg.common.util.ReflectHelper;
import org.jeecg.common.util.dynamic.db.DataSourceCachePool;
import org.jeecg.common.util.dynamic.db.DynamicDBUtil;
import org.jeecg.modules.online.cgform.util.CgformUtil;
import org.jeecg.modules.online.config.util.ConditionHandler;

import javax.servlet.http.HttpServletRequest;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @Description: 根据不同的数据库，动态生成分页SQL
 * @Author scott
 * @Date 2019-03-20
 */
@Slf4j
public class CgReportSqlUtil {

    /**
     * sql 语句关键词
     */
    public static final String SQL_WHERE = " where ";
    public static final String SQL_AND = " and ";
    public static final String SQL_OR = " or ";

//    /**
//     * 分页SQL
//     */
//    public static final String MYSQL_SQL = "select * from ( {0}) sel_tab00 limit {1},{2}";         //mysql
//    public static final String POSTGRE_SQL = "select * from ( {0}) sel_tab00 limit {2} offset {1}";//postgresql
//    public static final String ORACLE_SQL = "select * from (select row_.*,rownum rownum_ from ({0}) row_ where rownum <= {1}) where rownum_>{2}"; //oracle
//    public static final String SQLSERVER_SQL = "select * from ( select row_number() over(order by tempColumn) tempRowNumber, * from (select top {1} tempColumn = 0, {0}) t ) tt where tempRowNumber > {2}"; //sqlserver


//    /**
//     * 生成分页查询sql
//     * @param sql
//     * @param page
//     * @param rows
//     * @return
//     */
//    public static String jeecgCreatePageSql(String sql,Map params, int page, int rows){
//    	//TODO 数据库链接URL，需要动态获取
//    	String dbTypeUrl = "jdbc:mysql://127.0.0.1:3306/jeecg-boot?characterEncoding=UTF-8&useUnicode=true&useSSL=false";
//        int beginNum = (page - 1) * rows;
//        String[] sqlParam = new String[3];
//        sqlParam[0] = sql;
//        sqlParam[1] = beginNum+"";
//        sqlParam[2] = rows+"";
//        if(dbTypeUrl.indexOf(DataBaseConstant.DB_TYPE_MYSQL)!=-1 || dbTypeUrl.indexOf(DataBaseConstant.DB_TYPE_MARIADB)!=-1){
//            sql = MessageFormat.format(MYSQL_SQL, sqlParam);
//        }else if(dbTypeUrl.indexOf(DataBaseConstant.DB_TYPE_POSTGRESQL)!=-1){
//            sql = MessageFormat.format(POSTGRE_SQL, sqlParam);
//        }else {
//            int beginIndex = (page-1)*rows;
//            int endIndex = beginIndex+rows;
//            sqlParam[2] = Integer.toString(beginIndex);
//            sqlParam[1] = Integer.toString(endIndex);
//            if(dbTypeUrl.indexOf(DataBaseConstant.DB_TYPE_ORACLE)!=-1) {
//                sql = MessageFormat.format(ORACLE_SQL, sqlParam);
//            } else if(dbTypeUrl.indexOf(DataBaseConstant.DB_TYPE_SQLSERVER)!=-1) {
//                sqlParam[0] = sql.substring(getAfterSelectInsertPoint(sql));
//                sql = MessageFormat.format(SQLSERVER_SQL, sqlParam);
//            }
//        }
//        return sql;
//    }
//
//    /**
//     * 生成分页查询sql
//     * @param sql
//     * @param page
//     * @param rows
//     * @return
//     */
//    @SuppressWarnings("rawtypes")
//	public static String jeecgCreatePageSql(String dbTypeUrl,String dbKey,String sql,Map params, int page, int rows){
//    	sql = getFullSql(sql,params);
//        int beginNum = (page - 1) * rows;
//        String[] sqlParam = new String[3];
//        sqlParam[0] = sql;
//        sqlParam[1] = beginNum+"";
//        sqlParam[2] = rows+"";
//        //TODO 动态数据源，获取数据库类型
//        String databaseType = "";
//        if(DataBaseConstant.DB_TYPE_MYSQL.equalsIgnoreCase(databaseType) || DataBaseConstant.DB_TYPE_MARIADB.equalsIgnoreCase(databaseType)){
//            sql = MessageFormat.format(MYSQL_SQL, sqlParam);
//        }else if(DataBaseConstant.DB_TYPE_POSTGRESQL.equalsIgnoreCase(databaseType)){
//            sql = MessageFormat.format(POSTGRE_SQL, sqlParam);
//        }else {
//            int beginIndex = (page-1)*rows;
//            int endIndex = beginIndex+rows;
//            sqlParam[2] = Integer.toString(beginIndex);
//            sqlParam[1] = Integer.toString(endIndex);
//            if(DataBaseConstant.DB_TYPE_ORACLE.equalsIgnoreCase(databaseType)) {
//                sql = MessageFormat.format(ORACLE_SQL, sqlParam);
//            } else if(DataBaseConstant.DB_TYPE_SQLSERVER.equalsIgnoreCase(databaseType)) {
//                sqlParam[0] = sql.substring(getAfterSelectInsertPoint(sql));
//                sql = MessageFormat.format(SQLSERVER_SQL, sqlParam);
//            }
//        }
//        return sql;
//    }
//
//    private static int getAfterSelectInsertPoint(String sql) {
//        int selectIndex = sql.toLowerCase().indexOf("select");
//        int selectDistinctIndex = sql.toLowerCase().indexOf("select distinct");
//        return selectIndex + (selectDistinctIndex == selectIndex ? 15 : 6);
//    }
//

    //update-begin-author:scott  date:20200324  for:LOWCOD-327 发现 online 报表好鸡肋啊，稍微复杂一点的sql就一直 sql解析失败
    /**
     * 【转换${}规则为万能SQL，方便SQL执行解析】
     *  示例： 将 id = '${id}' 替换为 1=1
     * @param sql
     * @return
     */
    public static String getUniversalSql(String sql){
        sql = sql.replaceAll("(?i)"+SQL_WHERE, SQL_WHERE);
        sql = sql.replaceAll("(?i)"+SQL_AND, SQL_AND);
        sql = sql.replaceAll("(?i)"+SQL_OR, SQL_OR);
        String regex = "(,\\s*|\\s*(\\w|\\.)+\\s*[^, ]+ *\\S*)\\$\\{\\w+\\}\\S*";
        Pattern p = Pattern.compile(regex);
        Matcher m = p.matcher(sql);
        while (m.find()) {
            String whereParam = m.group();
            //log.debug("${}匹配带参SQL片段 ==>" + whereParam);
            if(whereParam.indexOf(SQL_WHERE)!=-1){
                String cloumn = whereParam.substring(0,whereParam.indexOf(SQL_WHERE));
                sql = sql.replace(whereParam,cloumn+" "+"where 1=1");
            }else if(whereParam.indexOf(SQL_AND)!=-1){
                whereParam=whereParam.substring(whereParam.indexOf("and"));
                if(whereParam.indexOf("(")>0){
                    whereParam=whereParam.substring(whereParam.indexOf("(")+1);
                    sql = sql.replace(whereParam, " 1=1 ");
                }else{
                    sql = sql.replace(whereParam, "and 1=1");
                }
            }else if(whereParam.indexOf(SQL_OR)!=-1){
                whereParam=whereParam.substring(whereParam.indexOf("or"));
                if(whereParam.indexOf("(")>0){
                    whereParam=whereParam.substring(whereParam.indexOf("(")+1);
                    sql = sql.replace(whereParam, " 1=1 ");
                }else{
                    sql = sql.replace(whereParam, "or 1=1");
                }
            } else if(whereParam.startsWith(",")){
                sql = sql.replace(whereParam, " ,1 ");
            }else{
                sql = sql.replace(whereParam, " 1=1 ");
            }
            //log.debug("${}替换后结果 ==>" + sql);
        }
        //防止 druid 异常 sql injection violation, double const condition
        sql = sql.replaceAll("(?i)\\(\\s*1=1\\s*(AND|OR)","(");
        sql = sql.replaceAll("(?i)(AND|OR)\\s*1=1","");
        return sql;
    }
    //update-end-author:scott  date:20200324  for:LOWCOD-327 发现 online 报表好鸡肋啊，稍微复杂一点的sql就一直 sql解析失败


//    public static void main(String[] args) {
//       String s =  "select * from sys_user where id   ='${id}' and del_flag=  ${flag}";
//        System.out.println(getUniversalSql(s));
//    }

	/**
	  * 获取页面查询参数
	 * @param request
	 * @return
	 */
	public static Map<String, Object> getParameterMap(HttpServletRequest request) {
		// 参数Map
		Map<?, ?> properties = request.getParameterMap();
		// 返回值Map
		Map<String, Object> returnMap = new HashMap<String, Object>();
		Iterator<?> entries = properties.entrySet().iterator();

		Map.Entry<String, Object> entry;
		String name = "";
		String value = "";
		Object valueObj =null;
		while (entries.hasNext()) {
			entry = (Map.Entry<String, Object>) entries.next();
			name = (String) entry.getKey();
			valueObj = entry.getValue();
			if ("_t".equals(name) || null == valueObj) {
				value = "";
			} else if (valueObj instanceof String[]) {
				String[] values = (String[]) valueObj;
				for (int i = 0; i < values.length; i++) {
					value = values[i] + ",";
				}
				value = value.substring(0, value.length() - 1);
			} else {
				value = valueObj.toString();
			}
			returnMap.put(name, value);
		}
		return returnMap;
	}

//    /**
//     * 报表sql支持权限配置 和系统内置变量
//     * @return
//     */
//	public static String rewriteReportSql(String sql){
//        String sql1 = QueryGenerator.convertSystemVariables(sql);
//        String sql2 = QueryGenerator.getAllConfigAuth();
//        if(sql.toLowerCase().indexOf("where")>0){
//            return sql1+sql2;
//        }else{
//            return sql1+CgformUtil.SQL_WHERE_TRUE +sql2;
//        }
//    }

    /**
     * 是不是以select开头
     * @param sql
     * @return
     */
    public static boolean beginWithSelect(String sql){
        String temp = sql.toLowerCase();
        return temp.indexOf("select")==0;
    }

    /**
     * 获取求数量 SQL
     *
     * @param sql
     * @return
     */
    public static String getCountSql(String sql) {
        return String.format("SELECT COUNT(1) \"total\" FROM ( %s ) temp_count", sql);
    }

    /**
     * 按照数据库类型，分页返回一条数据
     *
     * @param dbKey 动态数据源key
     * @param sql  动态sql
     * @return
     */
    public static Map<String, Object> parseOneByPageSql(String dbKey, String sql) {
        Map<String, Object> objectMap = null;
        DynamicDataSourceModel dbSource = DataSourceCachePool.getCacheDynamicDataSourceModel(dbKey);
        //通过mybatisPlus的底层方法获取db类型
        DbType dbType = JdbcUtils.getDbType(dbSource.getDbUrl());
        IDialect dialect = DialectFactory.getDialect(dbType);
        Page page = new Page<>(1, 1);
        DialectModel model = dialect.buildPaginationSql(sql, page.offset(), page.getSize());
        String pageSQL = model.getDialectSql();
        //log.info("parse sql with page : "+ pageSQL);
        if(pageSQL.contains("?")){
            //TODO mybatisplus的SqlServer下排序分页有bug
            long firstParam = (long) ReflectHelper.getFieldVal("firstParam",model);
            long secondParam = (long) ReflectHelper.getFieldVal("secondParam",model);
            int n = pageSQL.length() - pageSQL.replaceAll("\\?", "").length();
            if (n == 1) {
                objectMap = (Map<String, Object>) DynamicDBUtil.findOne(dbKey, pageSQL,firstParam);
            } else {
                objectMap = (Map<String, Object>) DynamicDBUtil.findOne(dbKey, pageSQL,firstParam,secondParam);
            }
        }else{
            objectMap = (Map<String, Object>) DynamicDBUtil.findOne(dbKey, pageSQL);
        }
        return objectMap;
    }

    /**
     * 按照数据库类型，返回分页数据
     *
     * @param isGetAll 是否获取全部（既不分页）
     * @param dbKey 动态数据源key
     * @param querySql  动态sql
     * @param pageNo  查询页
     * @param pageSize 页数量
     * @return
     */
    public static List<Map<String, Object>> getAutoPageList(String isGetAll, String dbKey, String querySql, int pageNo, int pageSize, Map<String,Object> sqlParam) {
        DynamicDataSourceModel dbSource = DataSourceCachePool.getCacheDynamicDataSourceModel(dbKey);
        String pageSQL = querySql;
        DialectModel model = null;
        List<Map<String, Object>> pageList = null;

        // 判断是否查询所有数据，是的话不分页
        if (!Boolean.valueOf(isGetAll)) {
            // pageSQL = SqlUtils.createPageSqlByDBType(dbSource.getDbType(), querySql, pageNo, pageSize);
            DbType dbType = JdbcUtils.getDbType(dbSource.getDbUrl());
            IDialect dialect = DialectFactory.getDialect(dbType);
            Page page = new Page<>(pageNo, pageSize);
            model = dialect.buildPaginationSql(querySql, page.offset(), page.getSize());
            pageSQL = model.getDialectSql();
        }

        //log.info("多数据源 报表查询sql=>1pageSQL: " + pageSQL);
        // sql语句中含有?号，则需要添加param
        if (pageSQL.contains("?")) {
            // TODO mybatisplus的SqlServer下排序分页有bug
            // 设置分页参数
            long firstParam = (long)ReflectHelper.getFieldVal("firstParam", model);
            long secondParam = (long)ReflectHelper.getFieldVal("secondParam", model);
            //log.info("多数据源 报表查询sql=>pageSQL 分页参数：firstParam=" + firstParam + " ,secondParam=" + secondParam);
            int n = pageSQL.length() - pageSQL.replaceAll("\\?", "").length();
            if (n == 1) {
                pageSQL = ConditionHandler.replaceSqlPageParam(pageSQL, firstParam);
            } else {
                pageSQL = ConditionHandler.replaceSqlPageParam(pageSQL, firstParam, secondParam);
            }
        }

        //log.info("多数据源 报表查询sql=>2pageSQL: " + pageSQL);
        //log.info("多数据源 报表查询sql=>sqlParam: " + sqlParam.toString());
        pageList = DynamicDBUtil.findListByNamedParam(dbKey, pageSQL, sqlParam);
        return pageList;
    }

}
